<%@ page language="java" contentType="text/html; charset=UTF-8"pageEncoding="UTF-8"%>
<%@ page import="java.sql.*" %>
<%@ page import="org.sqlite.*" %>
<%@ page import="java.text.DateFormat" %>
<%@ page import="java.text.NumberFormat" %>
<%@ page import="java.text.DecimalFormat" %>
<%@ page import="java.util.Date"%>
<%@ page import="java.util.Calendar"%>
<%@ page import="java.io.*"%>
<%@ page import="com.lowagie.text.pdf.*"%>
<%@ page import="com.lowagie.text.*"%>

<%!
public static DateFormat df = new java.text.SimpleDateFormat("dd/MM/yy HH:mm:ss");
%>
<%!
public static NumberFormat nf = new DecimalFormat("#####.##");
%>
<%!
public static Connection conn;
%>
<%!
public static Statement stat;
%>
<%!
public static ResultSet rs;
%>
<%!
public static File fileTempCsv;
%>
<%!
public static File fileTempPdf;
%>

<html>


  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
    <link rel= "SHORTCUT ICON" href="http://www.econorma.com/econorma.ico"/>
    <link rel="stylesheet" href="http://code.jquery.com/ui/1.9.2/themes/base/jquery-ui.css" />
    <style type='text/css'>
      #content {padding: 1%;}.inner-box {background: #000000;}.inner-box .col
      {background:#E8E8E8;display:block;float:left;margin:1%;padding:1%;width:21%;box-shadow:inset rgba(255,255,255,0.5)
      8px 8px 15px 5px,inset rgba(0,0,0,0.3) -2px -2px 15px 5px;}
    </style>
    <script src="http://code.jquery.com/jquery-1.8.3.js">
    </script>
    <script src="http://code.jquery.com/ui/1.9.2/jquery-ui.js">
    </script>
    <script>
      $(function() {
        $( "#datepickerDa" ).datepicker({ dateFormat: 'dd/mm/yy' });
        });
        $(function() {
          $( "#datepickerA" ).datepicker({ dateFormat: 'dd/mm/yy' });
          });
        </script>

        <script>
          function myFunction()
          {

            alert("Hello World!");

          }
        </script>


        <title>
          ECONORMA JSP
        </title>

      </head>


      <table width="100%"  border="1">


        <tr valign="top">
          <td style="background-color:#eeeeee;width:20%; text-align:top;padding-left:10px;">


          </select>
          <br>
        </select>
        <br>

        <form method="post">


          Sonda &nbsp&nbsp:
          <select name="idSonda">


            <%
            Class.forName("org.sqlite.JDBC");
            conn = DriverManager.getConnection("jdbc:sqlite:database.db");

            stat = conn.createStatement();

            rs = stat.executeQuery("select sonde.id_sonda from sonde");

            while (rs.next()) {
              out.println("<option value="+"\""+rs.getString(1)+"\">"  +  rs.getString(1) + "</option>");
            }

            rs.close();
            conn.close();
            %>



            </select>

            <br>
            <br>
            Data Da:
            <input type="text" id="datepickerDa" name="dataDa" />
            </select>
            <br>
            </select>
            <br>
            Data A &nbsp:
            <input type="text" id="datepickerA" name="dataA" />
            </select>
            <br>
            <br>
            </select>
            <br>
            <input type="submit" name="Visualizza" value="Visualizza"style="height:30px; width:100px">
            <br>
            </select>
            <br>
            </select>
            <br>
            </select>
            <br>
            </select>
            <br>
            </select>
            <br>
            </select>
            <br>
            </form>

            </td>

            <td style="background-color:#eeeeee;height:950px;
            width:40%;text-align:top;">

            <div style="height:880px;overflow:auto;">


            <table class="box" border="1px" align="center">

            <thead>
            <tr>
            <th style="width:20%;">
            Id Sonda
            </th>
            <th style="width:40%;">
            Descrizione
            </th>
            <th style="width:30%;">
            Data
            </th>
            <th style="width:10%;">
            Temperatura C
            </th>
            </tr>
            </thead>
            <tbody>
            </tbody>

            <%
            if(request.getParameter("Visualizza") != null) {

              String sonda = request.getParameter("idSonda");
              String dataDa = request.getParameter("dataDa");
              String dataA = request.getParameter("dataA");

              Date da;
              Date daX;
              Date daY;
              Date a;
              Calendar calDa;
              Calendar calA;

              FileWriter fw =null;

              DateFormat formatter = new java.text.SimpleDateFormat("dd/MM/yy");

              String where = "where letture_prova.id_sonda=sonde.id_sonda and letture_prova.id_sonda=" + "'" + sonda + "'";


              if (!dataDa.trim().equals("")) {
                da = formatter.parse(dataDa);

                calDa = Calendar.getInstance();
                calDa.setTime(da);
                calDa.set(Calendar.HOUR, 01);
                calDa.set(Calendar.MINUTE, 00);
                calDa.set(Calendar.SECOND, 0);
                calDa.set(Calendar.MILLISECOND, 0);

                daX = df.parse(df.format(calDa.getTime()));
                where = where.trim() + " and letture_prova.data >=" + daX.getTime();

              }

              if (!dataA.trim().equals(""))  {
                a = formatter.parse(dataA);

                calA = Calendar.getInstance();
                calA.setTime(a);
                calA.set(Calendar.HOUR, 23);
                calA.set(Calendar.MINUTE, 59);
                calA.set(Calendar.SECOND, 0);
                calA.set(Calendar.MILLISECOND, 0);

                daY = df.parse(df.format(calA.getTime()));
                where = where.trim() + " and letture_prova.data <=" + daY.getTime();
              }

 		 	  
		 	  File dir = new File("src/com/resources/jsp");
		 	  fileTempCsv = File.createTempFile("export", ".csv", dir);
		 	  fileTempPdf = File.createTempFile("report", ".pdf", dir);
		 	   
 			  fw = new FileWriter(fileTempCsv);
              fw.append("Id Sonda");
              fw.append(";");
              fw.append("Descrizione");
              fw.append(";");
              fw.append("Data");
              fw.append(";");
              fw.append("Temperatura");
              fw.append("\n");
              
              Document document = new Document();
              PdfWriter pdfWriter = PdfWriter.getInstance(document, new FileOutputStream(fileTempPdf));
              document.open();
              
              PdfPTable table = new PdfPTable(4);
               
			  PdfPCell c1 = new PdfPCell(new Phrase("Id Sonda"));
     		  c1.setFixedHeight(30f);
   			  c1.setHorizontalAlignment(Element.ALIGN_CENTER);
   			  table.addCell(c1);
   			 
    		  c1 = new PdfPCell(new Phrase("Descrizione"));
    		  c1.setHorizontalAlignment(Element.ALIGN_CENTER);
    		  table.addCell(c1);

    		  c1 = new PdfPCell(new Phrase("Data"));
  			  c1.setHorizontalAlignment(Element.ALIGN_CENTER);
              table.addCell(c1);
              
              c1 = new PdfPCell(new Phrase("Temperatura"));
  			  c1.setHorizontalAlignment(Element.ALIGN_CENTER);
              table.addCell(c1);
            
              table.setHeaderRows(1);
			  

              Class.forName("org.sqlite.JDBC");
              conn = DriverManager.getConnection("jdbc:sqlite:database.db");

              stat = conn.createStatement();

              rs = stat.executeQuery("select letture_prova.id_sonda, sonde.descrizione, letture_prova.data, letture_prova.valore from letture_prova, sonde " + where);


              while (rs.next()) {

                out.println("<tr>");
                out.println("<td align="+"center>" + rs.getString(1) + "</td>");
                out.println("<td style=padding-left:5px>" + rs.getString(2) + "</td>");
                out.println("<td align="+"center>" + df.format(rs.getDate(3)) + "</td>");
                out.println("<td align="+"center>" + nf.format(rs.getDouble(4)) + "</td>");
                out.println("</tr>");

                fw.append(rs.getString(1));
                fw.append(";");
                fw.append(rs.getString(2));
                fw.append(";");
                fw.append(df.format(rs.getDate(3)));
                fw.append(";");
                fw.append(nf.format(rs.getDouble(4)));
                fw.append("\n");
                
                table.addCell(rs.getString(1));
 			    table.addCell(rs.getString(2));
 			    table.addCell(df.format(rs.getDate(3)));
			    PdfPCell c2 = new PdfPCell(new Phrase(nf.format(rs.getDouble(4))));
			    c2.setHorizontalAlignment(Element.ALIGN_CENTER);
			    table.addCell(c2);
			 
              }
              
              
              float[] columnWidths = new float[] {10f, 30f, 20f, 15f};
              table.setWidths(columnWidths);
              document.add(table); 
              document.close();

              rs.close();
              conn.close();

              fw.flush();
              fw.close();
              
           
            }



            %>

            </table>


            </div>

            <div style="height:50px;overflow:auto;">
            <br>
            
              <%
              if (fileTempCsv!=null) {
              out.println("<a href=" + fileTempCsv.getName() + ">");
              } else {
              out.println("<a href>");
              }
     		  out.println("<input type=\"button\" name=\"Download\" value=\"Download\" style=\"height:30px; width:100px; margin-left:460px; \">");
              out.println("</a>");
              %>
              
              <%
              if (fileTempPdf!=null) {
              out.println("<a href=" + fileTempPdf.getName() + ">");
              } else {
              out.println("<a href>");
              }
     		  out.println("<input type=\"button\" name=\"Stampa\" value=\"Stampa\" style=\"height:30px; width:100px\">");
              out.println("</a>");
              %>
             
            </div>

            </td>

            <td style="background-color:#eeeeee;
            width:40%;text-align:top;padding-top:25px">
            <%
            String sonda = request.getParameter("idSonda");
            String dataDa = request.getParameter("dataDa");
            String dataA = request.getParameter("dataA");


            out.println("<IMG SRC=\"getChart.jsp?sonda=" + sonda + "&dataDa=" + dataDa + "&dataA=" + dataA + "\"" + "/>");
            %>


          </td>
        </tr>


        <table>


        </html>

